Forum Discussion
This post is deleted
Super UserHi Bertje123456,
As I am not confortable in Power Query 😞
Please find the DAX version to solve the above problem.
1. Create a new table which can help
ExceedanceType = DATATABLE(
"ExceedType", STRING,
{
{ "3 exceedances within limit" },
{ "3 exceedances beyond limit" }
}
)
2.Create below measures:
ExceedancesWithinLimit =
CALCULATE(
DISTINCTCOUNT('VW_AANSLUITING_VERBRUIK_ELK'[EAN code]),
FILTER(
'VW_AANSLUITING_VERBRUIK_ELK',
'VW_AANSLUITING_VERBRUIK_ELK'[Number of months] = 3
&& 'VW_AANSLUITING_VERBRUIK_ELK'[kW transport capacity exceedance] > 0
),
RELATED('VW_AANSLUITING_VERBRUIK_ELK'[kW transport capacity exceedance]) <= RELATED('VW_D_Transportbeperking'[Limit])
)
ExceedancesBeyondLimit =
CALCULATE(
DISTINCTCOUNT('VW_AANSLUITING_VERBRUIK_ELK'[EAN code]),
FILTER(
'VW_AANSLUITING_VERBRUIK_ELK',
'VW_AANSLUITING_VERBRUIK_ELK'[Number of months] = 3
&& 'VW_AANSLUITING_VERBRUIK_ELK'[kW transport capacity exceedance] > 0
),
RELATED('VW_AANSLUITING_VERBRUIK_ELK'[kW transport capacity exceedance]) > RELATED('VW_D_Transportbeperking'[Limit])
)
3. Create a unified measure:
ExceedanceCount =
SWITCH(
SELECTEDVALUE('ExceedanceType'[ExceedType]),
"3 exceedances within limit", [ExceedancesWithinLimit],
"3 exceedances beyond limit", [ExceedancesBeyondLimit],
BLANK()
)
4.Build your matrix visual
Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi