Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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, Sale C. I have included the DistCountA, DistCountB, DistCountC and Overall columns so I can more easily explain how to get the final value at the end but these columns are not given to me. Firstly, I am trying to get the distinct count of Sales for each store.
So for Store X, in the Sale A column there is only a single unique value. For Store Y in the Sale A column there are 2 unique values and both these values ends up in the DistCountA Column for each store. This happens also for DistCountB and DistCountC and the different stores. If the number of distinct values is equal to 1 for DistCountA, DistCountB and DistCountC, then the final result in the Overall column is 1, otherwise it is 0.
I then want to sum all the values in the Overall column, in this case, it is 2. I want a measure that will return the value 2 in a card. Is this possible using a measure only?
Store | Sale A | Sale B | Sale C | DistCount A | DistCount B | DistCount C | Overall | |||||||
X | 1 | 5 | 9 | 1 | 2 | 1 | 0 | |||||||
X | 1 | 6 | 9 | 1 | 2 | 1 | 0 | |||||||
Y | 2 | 4 | 8 | 2 | 1 | 1 | 0 | |||||||
Y | 3 | 4 | 8 | 2 | 1 | 1 | 0 | |||||||
Z | 4 | 1 | 7 | 1 | 1 | 1 | 1 | |||||||
Z | 4 | 1 | 7 | 1 | 1 | 1 | 1 |
The reason I want it to be a measure is that there are additional columns like date that users can filter on and that will change the results. For this example, I have omitted it since I want to give a more simplified version.
Solved! Go to Solution.
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
Proud to be a Super User!
Hi,
Would you be amenable to changing the structure of the table using Power Query i.e. converting the first 4 columns into just 3 - Store, Sale and Product? If you are OK with this approach, please post back.
Hi Anish, no i cannot change the table structure for this query.
I have tried a different approach based on some work by @DataInsights but it did not work.
I tried to create 3 measures, one for A - C. I've got additional fields, not just store that I want to group by in the below but I only posted store to keep things simple.
DistinctCountA = CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )
DistinctCountB = CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )
DistinctCountC = CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )
In a table or card, this gives me the correct result for DistinctCountA/B/C.
I then tried to create a measure after this to get the Overall column like the below.
Overall =
if(DistinctCountA = 1 && DistinctCountB = 1 && DistinctCountC =1, 1, 0)
When I did this, did not work properly. In the table, each row would be a 1, but the total would be 0 at the bottom of the table or in a card so I am stuck at this step
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
Proud to be a Super User!
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.
Proud to be a Super User!
It did not work perfectly for me, but it helped me go down a path to find the answer I was after.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
98 | |
89 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |