Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need some help creating a measure that I can display on a card that is the count of the number of times an item only appears in the list once. I need the ability to slice this according to date or date range.
Sample Data
Request_table | ||
RequestID | ICAO | Date |
1 | BIKF | 1/31/2019 |
1 | CYVR | 1/31/2019 |
2 | EDFH | 4/20/2019 |
3 | EGSS | 3/22/2019 |
3 | KBIF | 3/22/2019 |
4 | KBWI | 5/2/2019 |
4 | KCID | 5/2/2019 |
7 | KCID | 6/4/2019 |
4 | KDAY | 5/2/2019 |
7 | KDAY | 6/4/2019 |
1 | KDFW | 1/31/2019 |
6 | KDFW | 10/5/2019 |
5 | KIAD | 12/2/2019 |
Total Requests = 13 |
Column with counts
ICAO | Count of ICAO |
BIKF | 1 |
CYVR | 1 |
EDFH | 1 |
EGSS | 1 |
KBIF | 1 |
KBWI | 1 |
KCID | 2 |
KDAY | 2 |
KDFW | 2 |
KIAD | 1 |
Distinct count = 10 |
Expected data set and the card would display the count 7
ICAO | Count of ICAO |
BIKF | 1 |
CYVR | 1 |
EDFH | 1 |
EGSS | 1 |
KBIF | 1 |
KBWI | 1 |
KIAD | 1 |
Count with only one request = 7 | |
Or the count of Request_table[ICAO] | |
that only appears in the list once |
How do i go about creating such a measure?
Solved! Go to Solution.
See if this works. I got a card with 7 shown.
All Unique Occurrances =
COUNTROWS (
FILTER (
SUMMARIZECOLUMNS (
'Request Table'[ICAO],
"Count", COUNTROWS ( 'Request Table' )
),
[Count] = 1
)
)
If you want a table to show up listing the ICAO numbers, you can use this measure:
Unique Occurance =
VAR Occurrences = COUNTROWS('Request Table')
RETURN
IF(Occurrences = 1, Occurrences, BLANK())
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee if this works. I got a card with 7 shown.
All Unique Occurrances =
COUNTROWS (
FILTER (
SUMMARIZECOLUMNS (
'Request Table'[ICAO],
"Count", COUNTROWS ( 'Request Table' )
),
[Count] = 1
)
)
If you want a table to show up listing the ICAO numbers, you can use this measure:
Unique Occurance =
VAR Occurrences = COUNTROWS('Request Table')
RETURN
IF(Occurrences = 1, Occurrences, BLANK())
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting