Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a table that has data like the below one:
Date | Name |
30-11-2022 | Denny |
30-11-2022 | Cathy |
30-11-2022 | Andrew |
30-11-2022 | Denny |
30-11-2022 | Gagan |
30-11-2022 | Cathy |
30-11-2022 | Siri |
31-12-2022 | Cathy |
31-12-2022 | Vivek |
31-12-2022 | Sunek |
31-12-2022 | Bastien |
31-12-2022 | Andrew |
31-12-2022 | Bastien |
31-12-2022 | Philip |
31-12-2022 | Andrew |
31-12-2022 | John |
31-12-2022 | Creda |
31-12-2022 | Barbara |
31-01-2023 | John |
31-01-2023 | Philip |
31-01-2023 | Creda |
31-01-2023 | Siri |
31-01-2023 | Siri |
31-01-2023 | John |
I need to create a measure , say "Unique count", that will mark the unique values in a column and the first instance of duplicates with 1 and the rest blank.
Please note that I have a 'Date' filter(Slicer) that user can select.
So the expected output of the above table will be as below:
1) All dates selected
Date | Name | Unique count |
30-11-2022 | Andrew | 1 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Barbara | 1 |
31-12-2022 | Bastien | 1 |
31-12-2022 | Bastien | 0 |
30-11-2022 | Cathy | 1 |
30-11-2022 | Cathy | 0 |
31-12-2022 | Cathy | 0 |
31-12-2022 | Creda | 1 |
31-01-2023 | Creda | 0 |
30-11-2022 | Denny | 1 |
30-11-2022 | Denny | 0 |
30-11-2022 | Gagan | 1 |
31-12-2022 | John | 1 |
31-01-2023 | John | 0 |
31-01-2023 | John | 0 |
31-12-2022 | Philip | 1 |
31-01-2023 | Philip | 0 |
30-11-2022 | Siri | 1 |
31-01-2023 | Siri | 0 |
31-01-2023 | Siri | 0 |
31-12-2022 | Sunek | 1 |
31-12-2022 | Vivek | 1 |
TOTAL | 12 |
2) Date = 30-11-2022
Date | Name | Unique count |
30-11-2022 | Andrew | 1 |
30-11-2022 | Cathy | 1 |
30-11-2022 | Cathy | 0 |
30-11-2022 | Denny | 1 |
30-11-2022 | Denny | 0 |
30-11-2022 | Gagan | 1 |
30-11-2022 | Siri | 1 |
TOTAL | 6 |
3) Date = 31-12-2022
Date | Name | Unique count |
31-12-2022 | Andrew | 1 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Barbara | 1 |
31-12-2022 | Bastien | 1 |
31-12-2022 | Bastien | 0 |
31-12-2022 | Cathy | 1 |
31-12-2022 | Creda | 1 |
31-12-2022 | John | 1 |
31-12-2022 | Philip | 1 |
31-12-2022 | Sunek | 1 |
31-12-2022 | Vivek | 1 |
TOTAL | 9 |
4) Date = 31-01-2023
Date | Name | Unique count |
31-01-2023 | Creda | 1 |
31-01-2023 | John | 1 |
31-01-2023 | John | 0 |
31-01-2023 | Philip | 1 |
31-01-2023 | Siri | 1 |
31-01-2023 | Siri | 0 |
To summarize, for each kind of filter user applies the measure should dynamically calculate the same way for the selected values.
I had posted the scenario earlier but I am trying to rephrase it for a better understanding of the issue i am facing.
Any help on the above scenario is appreciated.
Thanks a lot.
Solved! Go to Solution.
try like:
1) add an index like:
2) plot all the column with a measure like:
measure =
VAR _index = MAX(TableName[Index])
VAR _name =MAX(TableName[Name])
VAR _indexmin =
MINX(
FILTER(
ALLSELECTED(TableName),
TableName[Name] =_name
),
TableName[Index]
)
RETURN
IF(
_indexmin = _index,1,0
)
it worked like:
p.s. An index column is needed to differeniate identical rows, otherwise duplicate rows will be hiden in visual.
try like:
1) add an index like:
2) plot all the column with a measure like:
measure =
VAR _index = MAX(TableName[Index])
VAR _name =MAX(TableName[Name])
VAR _indexmin =
MINX(
FILTER(
ALLSELECTED(TableName),
TableName[Name] =_name
),
TableName[Index]
)
RETURN
IF(
_indexmin = _index,1,0
)
it worked like:
p.s. An index column is needed to differeniate identical rows, otherwise duplicate rows will be hiden in visual.
Thanks a lot. That worked for me!!
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |