Replace a manual entered number with a value from another table and field

[moshiera]

I have two tables Systems and Table.  I'm trying to get the the total count of systems that we have for the most current version.  I can get it to work if I just manually type in the value  of the current version into the fomula but I wanted to use the table if I can.  I tried using a lookupvalue but it won't let me use that I get an error.  In the end I would like to do this for every software versions that we have.

Current Formula that works: 

BRP Total = CALCULATE(COUNTA('Table Systems'[Software BBP Version]), 'Table Systems'[Software BBP Version]= "3.25.7")

New Forumla = ?

Any help will be greatly appreicated! 

[Ashish_Mathur]

Hi,

Using the Query Editor, unpivot all columns of the first table other than the first one.  Create a relationship from the Value column of Table1 to the Current version column of Table2.  To your visual, drag the 2 columns of Table2.  Write this measure

Measure = countrows('Table1')

Hope this helps.

[moshiera]

I understand the unpivot part and create a realtionship on the version columns between the 2 tables.  But I don't think i understand the "To your visual, drag the 2 columns of Table2. "  and when I do the your measure it just gives me the total number of rows it does not distquish from the different versions and I want a total count of systems that have the most current version of each software.  My example only listed 2 but there are actually 15 that we are looking at.  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[PaulDBrown]

@moshiera 

Instead of the fixed value, try:

BRP Total = CALCULATE(COUNTA('Table Systems'[Software BBP Version]), FILTER( ALL(Table Systems), 'Table Systems'[Software BBP Version]= 

SELECTEDVALUE(Table Systems [Software BBP Version])))

[moshiera]

Thanks but that did not work.  I received an error about using a true/false expresion is not allowed.