Dax Formula for the count of Unique value

[DSPVarma]

Please find the attached screenshot. I want a DAX formula that should assign a value of 1 to the first occurrence of each unique value in the "UBID" column of the "DSRReport" table, and 0 to subsequent occurrences of the same value.

In Excel, I used the following formula to get the result '=IF(COUNTIF($B$1:B2,[@Product])>1,0,1)', but I am unable to get it in DAX. Please help me with this. Thanks in advance.

[qqqqqwwwweeerrr · Accepted solution]

Hi @DSPVarma 

This is just a work around. If you get stuck you can use this solution. 

For this i have created one more column "Index in power query and first short your data in power query by product and create new column index"

once you create this than you can create column using this Dax 

Flag =

var _index= CALCULATE(MIN('Sheet10'[Index]),ALLEXCEPT('Sheet10','Sheet10'[Product]))

return IF('Sheet10'[Index]= _index,1,0)
This is my output
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more interesting solution here: www.youtube.com/@Howtosolveprobem

Regards

[Ritaf1983 · Accepted solution]

Hi @DSPVarma 
You can achieve the goal with the power query.

The steps are :
1. Group the rows with the option "All rows" 

2. Add a custom column with "Table.AddIndexColumn"

3. Remove other columns 

4. Expend 

5. Add a conditional column that based on the index column returns 1 / 0 :

6. Remove unnecessary index column

Result:

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[qqqqqwwwweeerrr · Accepted solution]

Hi @DSPVarma 

This is just a work around. If you get stuck you can use this solution. 

For this i have created one more column "Index in power query and first short your data in power query by product and create new column index"

once you create this than you can create column using this Dax 

Flag =

var _index= CALCULATE(MIN('Sheet10'[Index]),ALLEXCEPT('Sheet10','Sheet10'[Product]))

return IF('Sheet10'[Index]= _index,1,0)
This is my output
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more interesting solution here: www.youtube.com/@Howtosolveprobem

Regards

[Zang_Mi]

Hello, with the first column as input data, you can use Query Editor in Power BI to create a custom column to obtain that result in 2 steps.

1. Index per Product. Group rows by Product column and add index to each group.
2. Result. If the value in Index per Product is equal to 1, then return 1, otherwise, return 0.

You can copy the following query to your dashboard and check how each step has been implemented.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZzDpAiZdwaQbEukOJj3wkp5g0kspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Index Group", each Table.AddIndexColumn(_, "Index", 1)}}),
    #"Expanded Index Group" = Table.ExpandTableColumn(#"Grouped Rows", "Index Group", {"Index"}, {"Index per Product"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Index Group", "Result", each if [Index per Product] = 1 then 1 else 0)
in
    #"Added Custom"