Forum Discussion

chrismark176's avatar
chrismark176
Frequent Visitor
8 years ago

Countifs for table using related table

Hi all,

I have two tables and need to create a relationship between both based on multiple columns

 

DATAREFERENCE

Each column in the DATA table is a specific drive time to a location. I would like to create a DAX formula that groups the individual times into the labels in the REFERENCE table. All the research I have found on this states a specific column is needed in the DAX to reference. My Excel training is telling me there is a way to create a nested IF statement, but I'm getting stuck.

Need Help

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi chrismark176,

     

    In Query Editor mode, duplicate the [Drive Time Label] column, then, split it by delimiter '-'.

     

    Unpivote the table structure of 'DATA' table.

     

    Duplicate [DI] column then split it.

     

    Save and apply all changes, return back to Data View mode. Create calculated tables like below:

    Cross Join =
FILTER (
    CROSSJOIN ( 'DATA', 'REFERENCE TABLE' ),
    OR (
        'DATA'[Label] >= 'REFERENCE TABLE'[Low]
            && 'DATA'[Label] < 'REFERENCE TABLE'[High],
        'DATA'[Label] >= 'REFERENCE TABLE'[Low]
            && 'REFERENCE TABLE'[High] = BLANK ()
    )
)

Summarize Table =
SUMMARIZE (
    'Cross Join',
    'Cross Join'[Drive Time Label],
    "Sum value", SUM ( 'Cross Join'[Value] )
)

    Result.

     

    Best regards,
    Yuliana Gu

    • chrismark176's avatar
      chrismark176
      Frequent Visitor
      8 years ago

      Thank you v-yulgu-msft for this. It solves another problem I had but I may have misrepresented what I needed in this ask:

       

      My ultimate objective is to have a column that takes those drive times, and when compared by other segments in the file, then label thier segment. Here is an example of what I am trying to finalize:

       

       

      My issue is that there are about 100 columns that have drive times and I do not want to create a calculated column for each individual task, but instead want to be able to interchange these columns in the visual and have the DRIVE TIME SEG column recalculate.

       

      Can that be done?

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi chrismark176,

         

         

        Please first unpivot DATA table in Query Editor.

         

        Save and apply all changes, then, create a calculated column in table view.

         

        Drive Time SEG =
IF (
    'DATA'[Value] > 0
        && 'DATA'[Value] <= 15,
    "0-15",
    IF (
        'DATA'[Value] > 15
            && 'DATA'[Value] <= 30,
        "15-30",
        IF (
            'DATA'[Value] > 30
                && 'DATA'[Value] <= 45,
            "30-45",
            IF (
                'DATA'[Value] > 45
                    && 'DATA'[Value] <= 60,
                "45-60",
                IF (
                    'DATA'[Value] > 60
                        && 'DATA'[Value] <= 75,
                    "60-75",
                    IF ( 'DATA'[Value] > 75 && 'DATA'[Value] <= 90, "75-90", "90+" )
                )
            )
        )
    )
)

         

         

        Then, add [Type] column into a slicer, use a matrix visual to host data like below.

         

        When you change the slicer item, matix will show the DRIVE TIME SEG column conditionally.

         

        Best regards,

        Yuliana Gu

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    Maybe an approach would be to create an index column in your 0-15, 15-30 etc table starting at zero, and then for each relevant item in your data table, you could create a measure using the quotient function dividing by 15, which'll then correspond with the index column?

    • chrismark176's avatar
      chrismark176
      Frequent Visitor
      8 years ago

      Thanks jthomson. Can you give me an example of how that would work? I'm still very new to BI and DAX.

       

       

      • jthomson's avatar
        jthomson
        Solution Sage
        8 years ago

        I think that would also fall down with your desire not to have many different measures etc for each time column you have unless I'm missing a quick way to duplicate the function, I'm mostly thinking of a quick way to find something that'll correspond with your time groups table