cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
timknox Regular Visitor
Regular Visitor

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

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Need help with some measures

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.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

1 REPLY 1
Ross73312 Super Contributor
Super Contributor

Re: Need help with some measures

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.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 2,264 guests
Please welcome our newest community members: