Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Relationships? Getting different slicers to work at the same time

Dear community,

I have an issue with my dashboard and I hope somebody can assist me 😊 I already received some great help previously, but I don’t get the final results I would want.

Attached you find my file: a Dashboard for a customer Service team.

The Idea behind the Dashboard: The user can utilize 4 slicers (Date Range; Country; Requestor; Frontend) and will see the respective visuals underneath. So far I only created 3, to give you an idea and to test, whether the slicers are working.

Problem: I can only get EITHER the date slicer, or all the other ones working and never all of them together. I guess its due to wrong relationships but I do not really know how to proceed with those.

Thank you very much for your help, it is highly appreciated!

Nik

File: https://drive.google.com/file/d/1Q-Imky3zWDUgPzjFHQj1YN0y8eoTk-D0/view?usp=sharing

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    My workaround is to convert two date columns into a single one, then create a relationship based on the single date column.

     

    First, open Edit qeries, select "created" and "closed" columns, then "Unpivot columns",

    For your scenario, since  "created" and "closed" columns include time part,

    you need to add a date column (Add column->Date->only date),

     

    Close and apply,

    create a relationship as below

     

    Create measures

    created ticket = CALCULATE(DISTINCTCOUNT(Table1[ticket number]),FILTER(ALLSELECTED(Table1),Table1[Attribute]="created"))

closed ticket = CALCULATE(DISTINCTCOUNT(Table1[ticket number]),FILTER(ALLSELECTED(Table1),Table1[Attribute]="closed"))

    Please download my pbix to see more details.

    There is another similar issue you can refer to : Filtering on 2 date fields in a table

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello Anonymous 

    There were a couple items I saw.

    1. Your visuals (count of created, count of closed, etc) are from the raw data table but the Dates table is not linked to your data table.
    2. The dates in the raw data table have a time stamp on them with make it not able to join to your date table properly.
    3. I'm assuming you want to filter on both the opened date and closed date?

    I made a copy of the PBIX where I added two calculated columns to the raw data table to calc just the opened and closed dates, leaving out the time.  I created a copy of the calender table by just referencing the existing one so now we have both an opened and a closed calendar.  Then I joined both calender tables to the raw data table using the two columns I created.

    Take a look: https://www.dropbox.com/s/qcfzbizm9lxkmfu/Power%20BI%20Dashboard_community.pbix?dl=0

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Jdbuchanan71, 

      thank you so much for your solution! Looks great and opened my eyes.

      But I would actually like to filter only on one Date slicer. Is that also possible? 

      Because I would like to look on a date range and see: How many tickets were opened, how many were closed etc. 

      Thank you very much! 

      Best, 
      Niklas 

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

        Hello Anonymous 

        We would have to write a measure to check if the opened date and closed date were both in the selected range, something like 

        IncludeRow = 
VAR OpenedRows = COUNTROWS( 'Raw Data' )
VAR ClosedRows = CALCULATE ( COUNTROWS ( 'Raw Data' ), USERELATIONSHIP ( 'Raw Data'[Closed Date], 'Date'[Date] ) )
RETURN IF ( NOT ISBLANK ( OpenedRows * ClosedRows ), 1, BLANK() )

        But measures cannot be used as page level filters or filters on cards so I don't think what you are looking for is possible, sorry.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    My workaround is to convert two date columns into a single one, then create a relationship based on the single date column.

     

    First, open Edit qeries, select "created" and "closed" columns, then "Unpivot columns",

    For your scenario, since  "created" and "closed" columns include time part,

    you need to add a date column (Add column->Date->only date),

     

    Close and apply,

    create a relationship as below

     

    Create measures

    created ticket = CALCULATE(DISTINCTCOUNT(Table1[ticket number]),FILTER(ALLSELECTED(Table1),Table1[Attribute]="created"))

closed ticket = CALCULATE(DISTINCTCOUNT(Table1[ticket number]),FILTER(ALLSELECTED(Table1),Table1[Attribute]="closed"))

    Please download my pbix to see more details.

    There is another similar issue you can refer to : Filtering on 2 date fields in a table

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.