Dynamic filter in a resume flag table

• jmolapa

  SSC Rookie

  Points: 44

  More actions

  January 14, 2019 at 9:59 am

  #376815

  I have thisscenario in a Tabular Cube (DAX) published in Azure Analysis Service.  
  Examples fact tables:
  Fact Sales Store

  Client ID	Data FK	Location Store	…	Sales_S_ID
  2	20181001	City A	…	1
  3	20181001	City B	…	2
  4	20181003	City A	…	3
  7	20181003	City B	…	4
  9	20181004	City B	…	5
  10	20181005	City A	…	6
  15	20181005	City A	…	7

   
  Fact Sales On-Line

  Client ID	Data FK	…	Sales_OnL_ID
  1	20181001	…	1
  3	20181002	…	2
  5	20181002	…	3
  6	20181003	…	4
  9	20181004	…	5
  10	20181005	…	6
  13	20181006	…	7

  
  

  In Power BI Desktop create a table with the Client ID, Sales_S_ID and Sales_OnL_ID: 

  Client ID	Sales_S_ID	Sales_OnLine_ID
  1		1
  2	1
  3	1	1
  4	1
  5		1
  6		1
  7	1
  9	1	1
  10	1
  13		1
  15	1
  Total	7	6

   
  So far so good, but I tried to create a Venn diagram just like the image below.

  

  I did not make it. To be able to use this visual data must be in this format.

  Client ID	Sales_S_ID	Sales_OnL_ID
  1	0	1
  2	1	0
  3	1	1
  4	1	0
  5	0	1
  6	0	1
  7	1	0
  9	1	1
  10	1	0
  13	0	1
  15	1	0
  Total	7	6

   

  Any suggestion? 

  I created in the Dim Client two columns with a Sales Store and Sales On-Line flag. 
  I filled out the columns with the following Formula: Sales Store = IF(LOOKUPVALUE(‘Fact Sales Store’[ID_Client]; ‘Fact SalesStore’[ID_Client];’Dim Client’[ID])<>0;1;0)

  DIM_Client

  ID	Name	…	…	…	Sales Store	Sales On-line
  1	…	…	…	…	0	1
  2	…	…	…	…	1	0
  3	…	…	…	…	1	1
  4	…	…	…	…	1	0
  5	…	…	…	…	0	1
  6	…	…	…	…	0	1
  7	…	…	…	…	1	0
  8	…	…	…	…	0	0
  9	…	…	…	…	1	1
  10	…	…	…	…	1	0
  11	…	…	…	…	0	0
  12	…	…	…	…	0	0
  13	…	…	…	…	0	1
  14	…	…	…	…	0	0
  15	…	…	…	…	0	1

  The result is not expected, ie as an example when I apply a filter in the DIM Location linked only to the Sales Store I was expecting it to be reflected in the Sales Client column of the Dim Client, it does not happen, the Dim Client is always unchanged. It needed to be dynamic by changing the value of the flag depending on the fact of the fact.

  Example: Filter the location for City B, so sales to customers in that city would have the flag repopulated, and Dim Client should look like this.
  The visual I am to use (Venn diagram) only allows to use columns, does not allow the use of metrics.

  ID	Name	Location	…	…	Sales Store	Sales On-line
  1	…	…	…	…	0	1
  2	…	…	…	…	1	0
  3	…	…	…	…	0	1
  4	…	…	…	…	1	0
  5	…	…	…	…	0	1
  6	…	…	…	…	0	1
  7	…	…	…	…	0	0
  8	…	…	…	…	0	0
  9	…	…	…	…	0	1
  10	…	…	…	…	1	0
  11	…	…	…	…	0	0
  12	…	…	…	…	0	0
  13	…	…	…	…	0	1
  14	…	…	…	…	0	0
  15	…	…	…	…	0	1

   

  Any suggestion? Any alternative? 

  I also tried to create a table calculated with a Summarize of the Customer Dimension, and the Facts tables, but it did not work either. It happened the same, the lines are initially calculated and are not recalculated / repopulated after applying the filters in the dimensions that relate to the factual.

  Tanks.

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  January 14, 2019 at 10:52 am

  #2018856

  Essentially, you're just checking if the Customer has Instore sales and/or Online sales?
  Can't you just do that with IF(COUNTROWS(RELATEDTABLE('Sales'))>1,1,0) and put it in the Customer table?  You might need FILTER() inside the COUNTROWS function to filter for "online"/"in store", though.

• jmolapa

  SSC Rookie

  Points: 44

  More actions

  January 15, 2019 at 3:05 am

  #2018909

  Thank you for the answer.
  In the initial post I forgot to put the template schema. I put it now.