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 would like to create two columns "Total Distributor" and "Source" based on the below data:
Here's the logic and I need some help transforming this to a calculated column or measure.
"Total Distributor" = Distinct count of "Distributor" that have "Status" = "Active" and grouped by "Item".
"Souce" = If "Total Distributor" > 1 then "Multiple", else "Single"
Item | Distributor | Status | Total Distributor | Source |
Mango | Royal Fruits | Active | 3 | Multiple |
Mango | Farmers Market | Active | 3 | Multiple |
Mango | Costco | Active | 3 | Multiple |
Apple | Costco | Active | 1 | Single |
Kiwi | Kroger | Active | 2 | Multiple |
Kiwi | Sams Club | Active | 2 | Multiple |
Grapes | Amazon | Active | 1 | Single |
Papaya | Walmart | Active | 1 | Single |
Papaya | Costco | Inactive | 1 | Single |
Could you please help?
Hi @SalHack ,
After my tests, I couldn't find a easy way to solve it.
If you have eight slicers, you need to create a slicer table with these columns. Then use ISFILTERED() to judge if it is selected. If true, return the selected value. If false, don't add it to filter.
According to my previous DAX, you need to nest eight IF() statements. This requires repeated work, look forward better solutions from other users.
I agree. This may work but it's too complex. Would anyone know if there's an easy way to address this?
@SalHack , Try a new column like
new Columns =
calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) && Table[Status] ="Active"))
new Columns =
if(calculate(distinctcount(Table[Distributor]), filter(Table, table[Item] = earlier(table[Item]) && Table[Status] ="Active")) >1,"Multiple","Single")
Thank you. The formula you shared above works fine when I do not have any filters applied on the data. However, I have several filters applied and I need to reflect the calculation based on what is selected and is visible on the screen. Could you advise how to do that?
Hi @SalHack ,
You need to create measures to get dynamic values.
To be independent with original table, you need to create a slicer table firstly( no relationship ).
Slicer =
SELECTCOLUMNS (
'Table',
"Item", 'Table'[Item],
"Distributor", 'Table'[Distributor]
)
Then refer to the following measures to get "Total Distributor" and "Source".
Total Distributor =
VAR a1 =
FILTER (
'Table',
'Table'[Item] = SELECTEDVALUE ( 'Slicer'[Item] )
&& 'Table'[Distributor] = SELECTEDVALUE ( 'Slicer'[Distributor] )
)
VAR a2 =
FILTER ( 'Table', 'Table'[Item] = SELECTEDVALUE ( 'Slicer'[Item] ) )
VAR a3 =
FILTER (
'Table',
'Table'[Distributor] = SELECTEDVALUE ( 'Slicer'[Distributor] )
)
VAR b =
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Item] ), 'Table'[Status] = "Active" )
RETURN
IF (
ISFILTERED ( 'Slicer'[Distributor] ) && ISFILTERED ( 'Slicer'[Item] ),
CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a1 ), b ),
IF (
ISFILTERED ( 'Slicer'[Distributor] )
&& ( ISFILTERED ( 'Slicer'[Item] ) = FALSE () ),
CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), a3 ), b ),
IF (
ISFILTERED ( 'Slicer'[Item] )
&& ( ISFILTERED ( 'Slicer'[Distributor] ) = FALSE () ),
CALCULATE ( CALCULATE ( COUNTA ( 'Table'[Item] ), b ), a2 ),
CALCULATE ( COUNTA ( 'Table'[Item] ), b )
)
)
)
Souce =
IF (
[Total Distributor] > 1,
"Multiple",
IF ( [Total Distributor] = 1, "Single" )
)
Here is my test file for your reference.
Thank you Eads. Couple of things:
1. Item is not part of the slicer
2. I have other colums as slicer that are not listed in the sample data below.
Based on the above changes, would the below script work?
so I have the following slicers:
Item1
Item2
Distributor
Item3
Item4
Item5
Item6
Item7
Item is part of the table view which has multiple columns.
Could you please help
is there a better solution to this requirement?
You can create two colums.
Total Distributor = COUNTX(FILTER('Sheet7',Sheet7[Item]=EARLIER(Sheet7[Item])&&Sheet7[Status]="Active"),Sheet7[Item])
source = if(Sheet7[Total Distributor]>1, "multiple","single")
Proud to be a Super User!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |