Forum Discussion

ctupps's avatar
ctupps
Frequent Visitor
4 years ago
Solved

Formatting data with live connection - create tables using functions similar to countifs

Hello,   I'm new to BI and am trying to figure out the best option for formatting data from a live survey database. I have not found exactly what I'm looking for in the training topics or forum, so...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi ctupps 
    Here is a sample file with the solution https://www.dropbox.com/t/gPKV4ZNVUhjVHqxY

    You can use power query to automatically append new datasets with master dataset. Create a list of all unique "flavors" and use the following measures

    Frequency = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        FILTER ( 
            Dtatset,
            CONTAINSSTRING ( Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) )
        )
    )
)
    No. of People = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 
                    Dtatset,
                    CONTAINSSTRING (  Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) ) 
                ),
                "@Name", Dtatset[name]
            )
        )
    )
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi ctupps 
Here is a sample file with the solution https://www.dropbox.com/t/gPKV4ZNVUhjVHqxY

You can use power query to automatically append new datasets with master dataset. Create a list of all unique "flavors" and use the following measures

Frequency = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        FILTER ( 
            Dtatset,
            CONTAINSSTRING ( Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) )
        )
    )
)
No. of People = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 
                    Dtatset,
                    CONTAINSSTRING (  Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) ) 
                ),
                "@Name", Dtatset[name]
            )
        )
    )
)
ctupps's avatar
ctupps
Frequent Visitor
4 years ago

Wonderful. Thank you so much!