Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have three tables as shown below. All three are unrelated and i dont want to move region name to country table.
I am looking for a calculated table as shown in output.
Is this possible? Any help on this is much appreciated. thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
Please try code as below to create a measure.
SUMX =
VAR _Summarize =
SUMMARIZE (
DimRegion,
DimRegion[Region Name],
"Amount",
VAR _Country =
CALCULATETABLE (
VALUES ( DimCountry[Country] ),
FILTER ( DimCountry, DimCountry[Region] = MAX ( DimRegion[Region] ) )
)
RETURN
CALCULATE (
SUM ( Country[Amount] ),
FILTER ( Country, Country[Country] IN _Country )
)
)
RETURN
SUMX ( _Summarize, [Amount] )
Data model:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try code as below to create a measure.
SUMX =
VAR _Summarize =
SUMMARIZE (
DimRegion,
DimRegion[Region Name],
"Amount",
VAR _Country =
CALCULATETABLE (
VALUES ( DimCountry[Country] ),
FILTER ( DimCountry, DimCountry[Region] = MAX ( DimRegion[Region] ) )
)
RETURN
CALCULATE (
SUM ( Country[Amount] ),
FILTER ( Country, Country[Country] IN _Country )
)
)
RETURN
SUMX ( _Summarize, [Amount] )
Data model:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft Thank you very much for your solution. it works without any error.
User | Count |
---|---|
93 | |
85 | |
78 | |
67 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |