Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Searching for values in a table based on another table

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

modelIDNo of SeatsEngine sizeNo of doorscruise controlheated seats
Ford123519955  
Ford456216002NN
Merc123725005 Y
Merc456513003NY
BMW111415004YN
BMW222425003NY
Ferrari1 30002NN
Mazda121 1YY
Mazda22312004  
Mazda33422005YY

 

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
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    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 () )
            )
    )
)

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        Hi Anonymous,

         

        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?