Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
timknox
Helper II
Helper II

Need help with some measures

I have a very large flat data file (300,000 lines and 25 columns), and a sample of the data would be:

 

ItemCode01/11/201801/12/201801/01/201901/02/201901/03/2019
1.1BDA256253215421
1.2BDD18221081560
1.3BCC1675104260
1.1BDA537294260
1.1BDA521294260
1.4BDC1421294260
1.3BCC1421294260
1.1Vulnerability1585912
1.2Vulnerability145963

 

I am trying to create a measure that will sumarize the data by CODE, including any associated 'Vulnerability' linked by the 'Item'.

 

So for example if I have a slicer as shown (which does not include 'Vulnerability' as it is not a unique Code):

 

Code
BDA
BDD
BCC
BDC

 

And i click on BDA, i get the following results on one report page:

 

ItemCode01/11/201801/12/201801/01/201901/02/201901/03/2019
1.1BDA3145391238141

 

ItemCode01/11/201801/12/201801/01/201901/02/201901/03/2019
1.1Vulnerability1585912

 

ItemCode01/11/201801/12/201801/01/201901/02/201901/03/2019
1.1TOTAL3296196247153

 

 I think i can achieve the first Matrix, but I am not sure how to achieve the other 2, including how to get the words 'Vulnerability' and 'Total' as shown.

 

Really appreciate any help.

 

Many thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

My recommendation is to split this data into 2 separate tables.  In the Edit Queries section, keep your current query as is, but add another step to unpivot the data.  This should get it into the format of Item, Code, Date, Value.

 

This query should now be set to "Enable Load" as off.

 

Next create 2 new queries that reference your original query.  Filter these tables such that vulnerability is in 1 table, and the codes are in the other.

 

Overall, every data point should exist in 1 table or the other.

 

Now, in the relationships area of power bi, link the tables together via the Item field.  From here you should be able to build your matrix showing the Code and its values, as well as the vulerabilities.

 

If you run into some troubles with the dates, i'd recommend a date table linked to both of your existing tables.

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

My recommendation is to split this data into 2 separate tables.  In the Edit Queries section, keep your current query as is, but add another step to unpivot the data.  This should get it into the format of Item, Code, Date, Value.

 

This query should now be set to "Enable Load" as off.

 

Next create 2 new queries that reference your original query.  Filter these tables such that vulnerability is in 1 table, and the codes are in the other.

 

Overall, every data point should exist in 1 table or the other.

 

Now, in the relationships area of power bi, link the tables together via the Item field.  From here you should be able to build your matrix showing the Code and its values, as well as the vulerabilities.

 

If you run into some troubles with the dates, i'd recommend a date table linked to both of your existing tables.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.