Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I have a Count problem that I can't seem to find an answer to.
I have two tables Events and Event_Categories:
Event |
ID |
111 |
112 |
113 |
114 |
Event_categories | |
Event_ID | Category |
111 | A |
111 | B |
112 | A |
113 | B |
114 | C |
I also have a slicer with the category values.
I want to create a measure with the distinct count of Event_ID where the Event_ID is present for all selected Category values.
For example: if I select from my slicer the values A and B the distinct count should return 1 (as only Event_ID 111 matches on the selected values - if I selected only category value B it should return a distinct count of 2).
I need the measure to also work on a table visual with the Event[ID] value, such as below for Category values A and B:
Event | |
ID | Distinct_count |
111 | 1 |
112 | 0 |
113 | 0 |
114 | 0 |
Any suggestions on how I can create a measure with the expected output would be greatly appreciated.
Hi
Please try this below DAX
cnt =
VAR _Combine =
CONCATENATEX(
Event_categories,
Event_categories[Event ID],","
)
VAR _CountRows=
COUNTX(
VALUES(Events[ID]),
_Combine
)
RETURN
_CountRows
Hi vk_pbi,
Thank you for the reply.
I tried the DAX and it returns a distinct count where the category is A OR B whereas I'm hoping to return a count where Event_ID is present for all of the selected slicer values (i.e. A and B):
In the above example the desired result would be 1
I think, i made it a bit complicated, but lets check it out
cnt =
VAR _NoofselectedValues =
COUNTROWS(ALLSELECTED(Event_categories[Category]))
VAR _Combine =
CONCATENATEX(
Event_categories,
Event_categories[Event_ID],","
)
VAR _Combine_cat =
CONCATENATEX(
Event_categories,
Event_categories[Category],","
)
VAR _CountRows=
IF(
NOT CONTAINSSTRING(
MAX(Event_categories[Category]),
_Combine_cat
),
COUNTX(
VALUES(Events[ID]),
_Combine
),
BLANK()
)
VAR _Result1 =
IF(
_NoofselectedValues >1,
_CountRows,
COUNTX( VALUES(Events[ID]),_Combine)
)
RETURN
_Result1
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
96 | |
94 | |
59 | |
45 | |
42 |