Forum Discussion
Solved
Power BI Measure That Is Like 'Over Partition' used in SQL
Hi All, I am trying to create a measure that can get me a single value that is similar to over partition in SQL. The logic I am after is like the below. I have the columns Store, Sale A, Sale B,...
Try this measure:
Sum Overall = VAR vBaseTable = ADDCOLUMNS ( Sales, "DistinctCountA", CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) ), "DistinctCountB", CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), ALLEXCEPT ( Sales, Sales[Store] ) ), "DistinctCountC", CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), ALLEXCEPT ( Sales, Sales[Store] ) ) ) VAR vFilterTable = FILTER ( vBaseTable, [DistinctCountA] = 1 && [DistinctCountB] = 1 && [DistinctCountC] = 1 ) VAR vResult = COUNTROWS ( vFilterTable ) RETURN vResult
Try this measure:
Sum Overall =
VAR vBaseTable =
ADDCOLUMNS (
Sales,
"DistinctCountA", CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
"DistinctCountB", CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
"DistinctCountC", CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), ALLEXCEPT ( Sales, Sales[Store] ) )
)
VAR vFilterTable =
FILTER (
vBaseTable,
[DistinctCountA] = 1
&& [DistinctCountB] = 1
&& [DistinctCountC] = 1
)
VAR vResult =
COUNTROWS ( vFilterTable )
RETURN
vResult
I've tried this and it seems to partially work but I'm not sure why.
When I put this part into a measure and add it to a table or card, it works flawlessly
distinctcountA = CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) )
When I then add it to vFilterTable it gives wildly different results. Is it because I'm filtering and this is not meant to be filtered?
Would you be able to provide more details about the issue and expected result? I created the solution based on your requirement below:
I want a measure that will return the value 2 in a card.It did not work perfectly for me, but it helped me go down a path to find the answer I was after.