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
I have a table like below:
ColumnA -- ColumnB -- Column3 -- Column4
123 DEV SRE DBA
123 MGR SRE NSE
112 DEV PMO NSE
112 DEV PMO NSE
Here ColumnA is the master column on which I want to perform the aggregated calculations. I want a result something like this:
A DEV MGR SRE PMO NSE DBA
123 1 1 2 0 1 1
112 2 0 0 2 2 0
I tried to calculate the SUM of DEV using the below formula
DEV =
CALCULATE ( COUNTROWS ( 'TableName' ), 'TableName'[ColumnB] = "DEV", 'TableName'[ColumnA] ='TableName'[ColumnA] )
But whenever I attempt the same for other values like MGR, SRE etc I face a circulare dependecy warning even though I am not using the DEV column for calculating the values
Here is the example for counting my MGR count
DEV =
CALCULATE ( COUNTROWS ( 'TableName' ), 'TableName'[ColumnB] = "MGR", 'TableName'[ColumnA] ='TableName'[ColumnA] )
Is there any better approach for this ? I am newbie in PowerBI. Started using it just today. So any pointers would be very helpful.
Solved! Go to Solution.
Personally I would unpivot the data into something like:
ColumnA | Position | Role
------------------------------
123 | ColumnB | DEV
123 | Column3 | SRE
...
To make the analysis dynamic you could create one measure - simple count of rows, and then use a Matrix visual - put ColumnA on the rows, column Role on columns and created measure on the values. That would work even if a particular "role" (DEV, DBA, etc.) can repeat in multiple columns in the original table.
--
But if you just need to perform a quick analysis on the data as it is, and the assumption can be done that a "role" (DEV, DBA, etc.) can show up in just one column, then you can easily create all necessary measures using Quick Measures feature. Example of how DEV measure could be configured with Filtered value calculation:
Then you can follow the above pattern for all other roles.
Hope this helps.
Cheers,
Pawel
Hi @karun_r,
You can unpivot table in Query Editor.
Alternatively, you can get the unpivot table structure via creating a calculated table.
Table1_1 = UNION ( SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[ColumnB] ), SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column3] ), SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column4] ) )
Based on the new table, you could use a matrix to display data, as adviced by pawelpo.
Best regards,
Yuliana Gu
Hi @karun_r,
You can unpivot table in Query Editor.
Alternatively, you can get the unpivot table structure via creating a calculated table.
Table1_1 = UNION ( SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[ColumnB] ), SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column3] ), SELECTCOLUMNS ( Table1, "Column1", Table1[ColumnA], "Column2", Table1[Column4] ) )
Based on the new table, you could use a matrix to display data, as adviced by pawelpo.
Best regards,
Yuliana Gu
Personally I would unpivot the data into something like:
ColumnA | Position | Role
------------------------------
123 | ColumnB | DEV
123 | Column3 | SRE
...
To make the analysis dynamic you could create one measure - simple count of rows, and then use a Matrix visual - put ColumnA on the rows, column Role on columns and created measure on the values. That would work even if a particular "role" (DEV, DBA, etc.) can repeat in multiple columns in the original table.
--
But if you just need to perform a quick analysis on the data as it is, and the assumption can be done that a "role" (DEV, DBA, etc.) can show up in just one column, then you can easily create all necessary measures using Quick Measures feature. Example of how DEV measure could be configured with Filtered value calculation:
Then you can follow the above pattern for all other roles.
Hope this helps.
Cheers,
Pawel
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 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |