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   Each column in the DATA table is a specific drive time to a location. I would like to...
Need Help
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