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've created a matrix visual based on this sample data:
PersonID WeekNr Service
1 12 A
1 12 B
1 13 A
1 13 B
1 14 A
1 15 A
2 12 A
2 13 B
2 15 A
2 15 B
2 16 A
2 16 B
3 12 B
3 13 B
3 13 A
3 14 B
3 15 A
3 15 B
and the values I present in the matrix is a result of the following measure:
Measure =
VAR A =
CALCULATE(
COUNTROWS(VALUES(Data[Service])),
ALLSELECTED(Data[Service])
)
RETURN
IF(
A > 1,
SUM(Data[Value]),
BLANK()
)
essentially giving me this:
So what it's doing is ONLY showing results for PersonID and Service, where both services (chosen from a simple slicer) are present during the same week number.
The matrix works great, no problems there at all.
But the client now wants to know, how many distinct PersonID there are in the matrix. I've tried a simple DISTINCTCOUNT(PersonID) but that's of course giving me the total of either value from the slicer. I then tried to use the same logic as in the above measure:
Measure =
VAR A =
CALCULATE(
COUNTROWS(VALUES(Data[Service])),
ALLSELECTED(Data[Service])
)
RETURN
IF(
A > 1,
DISTINCTCOUNT(Data[PersonID])/A,
BLANK()
)
but this works per line - a total of the results gives me an incorrect total (A manual count for example shows 30, but the result total in the card is 32...).
I hope this is clear enough - basically I need to take the results in the matrix (that works), and make a card displaying the number of PersonID that are displayed in the matrix.
Solved! Go to Solution.
Hi @grggmrtn ,
this has to do with contex of the measure try the following:
Measure 2 =
var temp_table = SUMMARIZE(Data; Data[PersonID];Data[WeekNr]; "@Value";[Measure])
return
CALCULATE (
DISTINCTCOUNT ( Data[PersonID] );
FILTER(temp_table; [@Value] > 0)
)
Replace the Data[WeekNr] by the date column, should work as expected.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHow about this expression?
Person Count = CALCULATE(DISTINCTCOUNT(Date[PersonID]), ALLSELECTED(Data))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Sorry, that's giving me the same result as just a simple DISTINCTCOUNT(Data[PersonID])
Hi @grggmrtn ,
Try the following measure:
Measure 2 =
CALCULATE (
DISTINCTCOUNT ( Data[PersonID] ),
FILTER ( Data, [Measure] <> BLANK () )
)
The [Measure] refered in this one is the first one you use for your matrix.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi @MFelix - thanks for the reply... the resulting card for this just shows '(Empty)' though 😞
Hi @grggmrtn ,
On the test I have made the result in the card is correct, as you can see below when I filter out the week the number of persons is changing accordingly. This model may not match with yours I used your sample data but instead of summing value i summed the week but the result is similar.
Are you abble to share a mockup file and expected result?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi again @MFelix - unfortunatly I'm not able to share a mockup file since it's all GDPR protected, and creating a dummy would take way too long.
I can see that it SHOULD be working - I just can't figure out why I'm getting an Empty result. My data matches the test data, I don't have any other filters that are getting in the way... frustrating 😞
Is your data only with the table you have show, or are there any other tables?
Be aware that measures are calculated based on context, meaning that any filters, slicers, visualizations settnigs, measure setup can influence the final result.
Do you have any other filtering applied on the report?
You refer that you have a slicer for the service person correct? how is that setup? maybe it's influencing the result of the second measure.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
There is only one table.
The matrix looks like this ("CPR" = PersionID, "Indsats Navn" = Service, columns are year, quarter, week number - "Borger Navn" is the Person's name and "Leverandør" is the provider, but neither of those should have an influence on the results)
There are 5 slicers on the page, but I have disabled them all. There are no filters.
Which makes the (Empty) result in the card just all the more strange...
Hi @grggmrtn ,
this has to do with contex of the measure try the following:
Measure 2 =
var temp_table = SUMMARIZE(Data; Data[PersonID];Data[WeekNr]; "@Value";[Measure])
return
CALCULATE (
DISTINCTCOUNT ( Data[PersonID] );
FILTER(temp_table; [@Value] > 0)
)
Replace the Data[WeekNr] by the date column, should work as expected.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelixyou're a genius. Works like a charm, and the sun is shining just a little bit brighter today. Thank you 🙂
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 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |