Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
A | B | |
1893492 | 3740100990 | |
1893492 | 3740100990 | |
1893492 | 3740100990 | |
1893492 | 3740100990 | |
1893492 | -1 | |
1902593 | 3740100991 | |
1902593 | 3740100991 | |
1902593 | 3740100991 | |
1902593 | -1 | |
1905279 | -1 | |
1905279 | -1 | |
1905279 | -1 | |
1905279 | -1 | |
1905279 | -1 | |
1905279 | -1 | |
1911152 | -1 | |
1911152 | -1 | |
1911152 | -1 | |
1911152 | -1 | |
1911152 | -1 | |
1911152 | -1 | |
1911152 | -1 |
Hi,
I wanted to distinct count values in A only when all corresponding values are -1 in B.
eg: in above table, 1905279 and 1911152 ate two values which has all its corresponding B values as -1.
ths output is 2 for this.
Any help is appreciated.
Thanks,
Apology, I did not check for all B values = -1
//Grouping data by A and B
Thanks @talespin , it gives single occurrences of unique combinations of A and B.
Hi, @Tamillllll
May I ask if this is the expected output you are looking for? Based on your description, I have created a new Table to achieve the effect you are looking for. Following picture shows the effect of the display.
New Table:
UniqueA =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
'Table',
'Table'[A],
"UniqueValuesInB", DISTINCTCOUNT ( 'Table'[B] )
),
"IsSingleValueInB", [UniqueValuesInB] = 1
),
[IsSingleValueInB]
),
[A]
)
If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi yang,
Thank you sp much for the solution, I have explained in the below screenshot and also provided data below.
Data:
JourneyID | Stop Sequence | Vehicle Id | Route |
1893494 | 1 | 3740000990 | 661 |
1893494 | 2 | 3740000990 | 661 |
1893494 | 3 | 3740000990 | 661 |
1893494 | 4 | 3740000990 | 661 |
1893494 | 5 | 3740000990 | 661 |
1893494 | 6 | 3740000990 | 661 |
1893494 | 7 | -1 | 661 |
1902590 | 1 | 3740000990 | 661 |
1902590 | 2 | 3740000990 | 661 |
1902590 | 3 | 3740000990 | 661 |
1902590 | 4 | 3740000990 | 661 |
1905277 | 1 | -1 | 661 |
1905277 | 2 | -1 | 661 |
1905277 | 3 | -1 | 661 |
1905277 | 4 | -1 | 661 |
1905277 | 5 | -1 | 661 |
1905277 | 6 | -1 | 661 |
1911151 | 1 | -1 | 661 |
1911151 | 2 | -1 | 661 |
1911151 | 3 | -1 | 661 |
1911151 | 4 | -1 | 661 |
1911151 | 5 | -1 | 661 |
1911151 | 6 | -1 | 661 |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrSwNDaxNFLSUTI2NzEwNDCwtDRQitWhkYSuIUTA0sDI1NIYWSXlEgizTY3MLWkuYGhoaGpEV4FYAA==",
BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"CountD", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([CountD] = 1))
in
#"Filtered Rows"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thank you so much for the solution, however its giving count of distinct values in B. I want to distinctcount column A when all its values is -1. As provided above in dax.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |