cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User
Super User

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!


   


1 REPLY 1
Super User
Super User

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!