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.
I am struggling with something that may be simple to solve. By year and month, I want to make a dynamic bar chart to present counts by a distinct ID that fits criteria based on columns Filter1 and Filter2. For simplicity, I am only using as an example a few lines from a large table, assuming all are from the same year and month.
ID | Filter1 | Filter2 |
ID1 | A | D |
ID1 | B | D |
ID2 | A | F |
ID2 | B | E |
ID3 | A | D |
ID3 | B | F |
ID4 | B | D |
ID4 | C | D |
ID5 | B | D |
ID5 | C | E |
ID6 | A | D |
ID6 | B | D |
ID6 | C | D |
An example of questions I am trying to answer and visualize are
1-How many times A and B were concomitantly present in an ID? Answer = 4 (ID1;ID2;ID3;ID6). This would later be used to answer a similar question applied to B and C, or for the three of them A, B, and C.
2- How many times A and B were concomitantly present in an ID, and A had Filter2= D and B had Filter2= D?
Answer= 2 (ID1; ID6)
2- How many times A and B were concomitantly present in an ID, and A had Filter2= D and B had Filter2= E?
Answer= 0
Any help in how I could do this is welcome!
Solved! Go to Solution.
@Giovani might these code help
ID_Count:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
VALUES ( SampleData[Filter1] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A" ), CONTAINSROW ( _Filter1, "B" ) )
)
)
----------------------------
ID_Count2:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "D" ) )
)
)
----------------------
ID_Count3:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "E" ) )
)
)
@Giovani might these code help
ID_Count:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
VALUES ( SampleData[Filter1] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A" ), CONTAINSROW ( _Filter1, "B" ) )
)
)
----------------------------
ID_Count2:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "D" ) )
)
)
----------------------
ID_Count3:=COUNTROWS (
FILTER (
VALUES ( SampleData[ID] ),
VAR _Filter1 =
CALCULATETABLE (
SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
ALLEXCEPT ( SampleData, SampleData[ID] )
)
RETURN
AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "E" ) )
)
)
Many thanks to @wdx223_Daniel this is perfect and is exactlly what I was looking for!
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 |
---|---|
54 | |
26 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |