Searching for values in a table based on another table

[ClaytonMUK]

Hi,

I want to populate a table with a count of values based on another table and a filter from a 2nd table. I've tried to replicate using dummy data attached, which is greatly simplified but the logic should be the same.

My dummy data considers specifications for cars. I have a table called ModelSpecs that lists various specs for cars. Here I have done 10 models and 5 specs but there could be lots more rows and cols. The modelID is unique and is my key.

My customer has provided 2 tables: RequiredModels lists the models they want details about and RequiredSpecs lists the Specs that they are interested in.

My desired output is a table like RequiredSpecs that has another column to shows how many of their models I have values for (ie not blank) for each spec. I don't want to show the value, just count if I have it.

For example, if I have Engine size data for 9 out of 10 cars on RequiredModels then that cell would show 9.

I have been able to calculate each value individually in a measure, but I cannot work out a way of iterating through each value in RequiredSpecs to populate that into a table - I've tried VAR in DAX and feel I'm almost there... and functions in Power Query but didn't get far with that. If you image that all 3 tables in the dummy data could be much bigger then it becomes a pain.

The modelID links ModelSpecs and RequiredModels. RequiredSpecs is not linked but the values in it will match the col headers in Model Specs.

An example of my individual measure is:

Values for Cruise Control = CALCULATE(

COUNTROWS(RequiredModels),

FILTER(ModelSpecs, ModelSpecs[cruise control] <>BLANK()))

I have copied the tables. Any guidance appreciated!

Martin

ModelSpecs

modelID	No of Seats	Engine size	No of doors	cruise control	heated seats
Ford123	5	1995	5
Ford456	2	1600	2	N	N
Merc123	7	2500	5		Y
Merc456	5	1300	3	N	Y
BMW111	4	1500	4	Y	N
BMW222	4	2500	3	N	Y
Ferrari1		3000	2	N	N
Mazda12	1		1	Y	Y
Mazda22	3	1200	4
Mazda33	4	2200	5	Y	Y

RequiredModels

modelID

BMW111

BMW222

BMW333

Ford123

Ford333

Ford444

Merc123

Merc456

Mazda12

Ferrari1

Required Specs:

Specification

No of Seats

Engine size

No of doors

cruise control

heated seats

[MFelix]

Hi @ClaytonMUK ,

To what I can understand you want to have the count of car that have values on each of the specifications so only non blank values create the following measure:

Spec count =
CALCULATE (
    SWITCH (
        SELECTEDVALUE ( RequireSpecs[Specification] ),
        "Cruise Control",
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS (
                        ALLSELECTED ( ModelsSpecs ),
                        "Cruise", ModelsSpecs[cruise control]
                    ),
                    NOT ( ISBLANK ( [Cruise] ) )
                )
            ),
        "Engine Size",
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS (
                        ALLSELECTED ( ModelsSpecs ),
                        "Engine", ModelsSpecs[Engine size]
                    ),
                    NOT ( ISBLANK ( [Engine] ) )
                )
            ),
        "Heated Seats",
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS (
                        ALLSELECTED ( ModelsSpecs ),
                        "Heated", ModelsSpecs[heated seats]
                    ),
                    NOT ( ISBLANK ( [Heated] ) )
                )
            ),
        "No of Doors",
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( ALLSELECTED ( ModelsSpecs ), "Doors", ModelsSpecs[No of doors] ),
                    NOT ( ISBLANK ( [Doors] ) )
                )
            ),
        "No of Seats",
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( ALLSELECTED ( ModelsSpecs ), "Seats", ModelsSpecs[No of Seats] ),
                    NOT ( ISBLANK ( [Seats] ) )
                )
            )
    )
)

Has you can see on attach PBIX when you filter the specs it's updated accordingly.

[ClaytonMUK]

Hi Miguel Felix,

Thanks for your help, but not quite what I need. Although there are 10 models in RequiredModels not all of them are listed in ModelSpecs - in other words some required models are not in my library eg BMW333. For example, I think No of doors spec count = 7.

Also, this is a proof of concept. I my real world my tables are much bigger so I cannot write such a measure. What I want to do is iterate through RequiredSpecs using the value for Specs as a variable.

[MFelix]

Hi @ClaytonMUK,

The best option would be to unpivot the specs tables, but believe that is also not an option.

What do you mean by

" What I want to do is iterate through RequiredSpecs using the value for Specs as a variable"

Also do you only want to count the values that are within the required models? 

Can you explain better what you want to achieve and the expected result with the mockup data you have? 

[ClaytonMUK]

Hi,

The RequiredSpecs table lists all the specifications my customer is interested in. (it is not all the possible specifications in my library). My desired output is a table showing each specification in RequiredSpecs and next to it a count of the number of times that specification is populated in ModelSpecs, but filtered by the models listed in RequiredModels.

I think I have achieved that measure for each specification, for example the result for Cruise Control is 5 using this measure:

Values for Cruise Control = CALCULATE(
COUNTROWS(RequiredModels),
FILTER(ModelSpecs, ModelSpecs[cruise control] <>BLANK()))

[MFelix · Accepted solution]

Hi @ClaytonMUK ,

That is similar to the one I have the only thing you have here is the fact that you are only doing it for a single spec, if you want to have all the spect you need to use the SWITCH function using your measure you woudl need to redo to something similar to this:

Spec count = 
CALCULATE (
    SWITCH (
        SELECTEDVALUE ( RequireSpecs[Specification] ),
        "Cruise Control",
            CALCULATE (
                COUNTROWS ( RequiredModels ),
                FILTER ( ModelsSpecs, ModelsSpecs[cruise control] <> BLANK () )
            ),
        "Engine Size",
            CALCULATE (
                COUNTROWS ( RequiredModels ),
                FILTER ( ModelsSpecs, ModelsSpecs[Engine size] <> BLANK () )
            ),
        "Heated Seats",
            CALCULATE (
                COUNTROWS ( RequiredModels ),
                FILTER ( ModelsSpecs, ModelsSpecs[heated seats] <> BLANK () )
            ),
        "No of Doors",
            CALCULATE (
                COUNTROWS ( RequiredModels ),
                FILTER ( ModelsSpecs, ModelsSpecs[No of doors] <> BLANK () )
            ),
        "No of Seats",
            CALCULATE (
                COUNTROWS ( RequiredModels ),
                FILTER ( ModelsSpecs, ModelsSpecs[No of Seats] <> BLANK () )
            )
    )
)

[ClaytonMUK]

Hi,

Yes that works, but seems a bit clunky to have to write out a Switch statement for each possible specification. I was trying to use a variable (using SELECTEDVALUE as you have) and use that somehow: eg ...FILTER(ModelsSpecs, ModelsSpecs[VARIABLE] <> Blank()

Anyway, thanks for the help. I'll build my giant Switch statement 🙂

[MFelix]

The issue with your data is that you have the specs on each column of the models data, that way you cannot do it in a different way since the result needs to be read in a different column each time.

If you had the models spec in a single column that way you could use a single measure to all.

Has you can see in attach file.,