Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I had a challenge figuring out how to show all the customers who had made sales in ALL of the user selected years.
The source data is simple: 3 columns: a) customer name, b) sales, c) the year of the sales. I want to have a page that user can select years available (as many as they want, not neccearily consecutive years) from a slicer, and then the table below will show all the customer names who have made sales in ALL of those user selected years. If a customer made sales only in some or none of the years selected, this customer won't show up in the table.
The source data is uploaded here.
I tried to use "INTERSECT" and even nested "INTERSECT" functions as illustrated by Sam McKay, but the problem is: I don't know how many years users will select (the number of years users can select is NOT fixed). therefore, I don't know how many times I have to nest the "INTERSECT". In the sample file, we have only five years (2015-2019). But in my real data source, I have total of 25 years.
Thank you for help.
PBISean
Solved! Go to Solution.
@Anonymous
Please see attached file as well
Hi @Anonymous
Thanks Sean for the coffee offer 🙂
Try this MEASURE as VISUAL level filter
Please see attached file as well
SalesofRepeatCustomers = VAR noofyears = COUNTROWS ( ALLSELECTED ( Sales[Year] ) ) RETURN IF ( SELECTEDVALUE ( Sales[Customer] ) IN FILTER ( ALLSELECTED ( Sales[Customer] ), VAR mytable = CALCULATETABLE ( VALUES ( Sales[Year] ) ) RETURN COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears ), 1 )
I get the following result
Hi @Anonymous
I will look at the attachment and get back to you
Regards,
Zubair
Hi @Anonymous
My apologies for late reply.
In the question tab, you can use this MEASURE. I have highlighted the changes from your formula in red color.
KEEPFILTERS will ensure that all context filters from inside the Matrix come into play
SalesofRepeatCustomersCalc = CALCULATE ( [Total Sales], KEEPFILTERS ( FILTER ( ALLSELECTED ( Sales[Customer] ), COUNTROWS ( FILTER ( ALLSELECTED ( Sales[Year] ), CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = [CountSelectedYears] ) ) )
The earlier version of the MEASURE was created to work as VISUAL filter. Thats why you dont see it in the Magic tab
@Anonymous
Please see attached file as well
Hi Zubair,
Thanks so much for your solution. I am still new to PBI and trying to learn. I kind of understand the logic of your solution, but not 100%.
what I need the output is a matrix visual that shows:
1. repeat customer names on the column
2. the selected years on the row
3. the sales of the customer in that selected year in the value
In the screenshot below, for example, the matrix will show the A, F, K, P, U on the column, the year 2015 & 2016 on the row, and the corresponding sales in the value area.
I tried hard to "tweak" your measure into the one I want but to no avail. I understand that this part:
No hurry, whenever you have some time to spare. Really appreciate your help. If you are in SF Bay Area, I will take you out for a coffee.
Hi @Anonymous
Thanks Sean for the coffee offer 🙂
Try this MEASURE as VISUAL level filter
Please see attached file as well
SalesofRepeatCustomers = VAR noofyears = COUNTROWS ( ALLSELECTED ( Sales[Year] ) ) RETURN IF ( SELECTEDVALUE ( Sales[Customer] ) IN FILTER ( ALLSELECTED ( Sales[Customer] ), VAR mytable = CALCULATETABLE ( VALUES ( Sales[Year] ) ) RETURN COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears ), 1 )
I get the following result
This is BRILIANT!
Hi Zubair,
Sorry to bother you again. Your solution definitely works and I really appreciate your help, but I still have difficulty understanding the measure formula. I uploaded the PBI file here. These are my questions:
1. On the "Magic" tab: I don't understand why the measure you created (now renamed "ZubairMagic" in the uploaded file) DOESN'T even show up in the matrix. Actually it doesn't show up in any visuals on the tab. The measure in the matrix is just the sum of native column of "Sales". When I replace the sum of native column of "Sales" with a measure I created "Total Sales", I expect the result won't change, but it actually won't work. How does ZubairMagic execute this magic?
2. On the "Question" tab: I understand your formula "RepeatCustomerList" as follows:
This part of the formula
gives a list of Customers who
a) have Non Zero sales, and
b) the number of years that they have Non Zero Sales is equal to the number of years user select (since their sales is aggregated into year, this means that in each of the selected years, the customer has Non Zero sales). This is brilliant!
Then, with the List of Customers (LoC) "harvested" in a row, the ConcatenateX turns them into a "flat" list seperate by ",", that's what Concatenate is supposed to do.
However, If I use the "harvested" list of customers (LoC) and feed them into a CALCULATE function, I expect that should work. Because Calculate should take this list of value and calculate the Total Sales. But obviously it won't work as shown in the file. Can we use a "Calculate" function to do the same? Why my approach fails?
I understand your time is precious. If you ignore my message here, i am completely OK.
Thank you
PBISean
Hi @Anonymous
I will look at the attachment and get back to you
Regards,
Zubair
THANK YOU!
Hi @Anonymous
My apologies for late reply.
In the question tab, you can use this MEASURE. I have highlighted the changes from your formula in red color.
KEEPFILTERS will ensure that all context filters from inside the Matrix come into play
SalesofRepeatCustomersCalc = CALCULATE ( [Total Sales], KEEPFILTERS ( FILTER ( ALLSELECTED ( Sales[Customer] ), COUNTROWS ( FILTER ( ALLSELECTED ( Sales[Year] ), CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = [CountSelectedYears] ) ) )
The earlier version of the MEASURE was created to work as VISUAL filter. Thats why you dont see it in the Magic tab
Hi Zubair,
This is so cool. I don't know you can put a measure into the visual level filter. Thank you soooo much.
Sean
Hi @Anonymous
Try this MEASURE
Do you need output of the customers in a card visual?
Measure = VAR noofyears = COUNTROWS ( ALLSELECTED ( Sales[Year] ) ) RETURN CONCATENATEX ( FILTER ( ALLSELECTED ( Sales[Customer] ), VAR mytable = CALCULATETABLE ( VALUES ( Sales[Year] ) ) RETURN COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears ), [Customer], "," )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |