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 log...
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 () )
            )
    )
)
MFelix's avatar
MFelix
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        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 () )
            )
    )
)