Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have sample data below:
ID | Source | Date |
123 | Source1 | 01/08/2022 |
123 | Source1 | 01/09/2022 |
123 | Source2 | 01/07/2022 |
456 | Source1 | 01/10/2022 |
789 | Source1 | 01/01/2022 |
101 | Source1 | 01/03/2022 |
102 | Source1 | 02/04/2022 |
103 | Source1 | 08/12/2022 |
103 | Source2 | 10/02/2022 |
I would like to count the ID's which ONLY have Source as Source1 before a selected date, e.g. 31/08/2022.
First, to filter the table down to Date <= 31/08/2022 I should get:
ID | Source | Date |
123 | Source1 | 01/08/2022 |
123 | Source2 | 01/07/2022 |
789 | Source1 | 01/01/2022 |
101 | Source1 | 01/03/2022 |
102 | Source1 | 02/04/2022 |
103 | Source2 | 10/02/2022 |
Then, I need to check whether an ID only ever had a source as Source1. My thoughts are a concatenate (however, I would welcome a different way of doing this) which will result in:
ID | Concat_Source |
123 | Source1, Source2 |
789 | Source2 |
101 | Source1 |
102 | Source1 |
103 | Source2 |
Finally, I want to check whether the concat_source ever contained Source 2. If it did, then I don't want to count it. In this case, only 101,102 would be counted so the result is 2 shown on a card visual.
I have been able to recreate the above logic with the following:
VAR lastdateselected = LASTDATE ( Date[Dates] ) //assume this is 31/08/2022 and comes from a date table.
Solved! Go to Solution.
Hi , @kush23456
According to your description, you want to get the count of the ID which date <= max slicer date you selected && only have "Source" . And the date table has a relationship between your tables, like this:
For your need , we can create two measures:
Measure = var _max_date_slicer = MAX('Date'[Date])
var _cur_id = MAX('Table'[ID])
var _t = FILTER( ALL('Table') ,'Table'[ID]=_cur_id && 'Table'[Date] <= _max_date_slicer)
return
CONCATENATEX(_t ,[Source],",")
Count = var _max_slicer= MAX('Date'[Date])
var _t = FILTER( ALL('Table') ,'Table'[Date]<=_max_slicer)
var _t2 = ADDCOLUMNS(_t , "flag" , var _id = [ID] var _source_count = COUNTROWS( FILTER(_t,[ID]=_id)) var _source =[Source] return IF(_source_count=1 && _source = "Source1",1,0))
return
SUMX(_t2,[flag])
Then we can put the fields on the visual and we can get your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @kush23456
According to your description, you want to get the count of the ID which date <= max slicer date you selected && only have "Source" . And the date table has a relationship between your tables, like this:
For your need , we can create two measures:
Measure = var _max_date_slicer = MAX('Date'[Date])
var _cur_id = MAX('Table'[ID])
var _t = FILTER( ALL('Table') ,'Table'[ID]=_cur_id && 'Table'[Date] <= _max_date_slicer)
return
CONCATENATEX(_t ,[Source],",")
Count = var _max_slicer= MAX('Date'[Date])
var _t = FILTER( ALL('Table') ,'Table'[Date]<=_max_slicer)
var _t2 = ADDCOLUMNS(_t , "flag" , var _id = [ID] var _source_count = COUNTROWS( FILTER(_t,[ID]=_id)) var _source =[Source] return IF(_source_count=1 && _source = "Source1",1,0))
return
SUMX(_t2,[flag])
Then we can put the fields on the visual and we can get your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks Aniya.
Unfortunately, the solution didn't work as expected as there was a little added complexity in the sense that your measure looks at where a column = Source1. The measure I was looking for was whether it contained Source1. Easily adaptable but still didn't work quite as expected. However, your answer gave me some inspiration and I particularly like the SUMX on the 1's and 0's that you suggested.
I managed to get to a solution through the use of virtual tables, summarize, filter, remove filters (to remove my dates filter and give me more control than using an ALL statement) and taking a slightly different approach to the logic of working this out than I originally did.
Thanks again for your valuable response!
@kush23456 , Use date in before of after slicer and try a measure like
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.