Forum Discussion

Dacja's avatar
Dacja
Frequent Visitor
4 years ago
Solved

Help: Who has time at a specific Date?

Hello community,

 

I have a "Date" Table created with CALENDAR function

I have a "Forecast Entry" query, which tracks which Person is planned for which Date. (fields: "Resource No.", Date)

I have a "Resource" query which has the fields "Resource No." and "Resource Name"

 

I want a new Table which shows me on which Date, which Person has nothing planned yet, to show that person in a calender visual.

Date is linked with Forecast Entry (through Date) and Resource is linked with Resource (Through Resource No.)

 

I'am stuck at creating that table.

 

Thanks alot!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Dacja ,

    I updated my sample pbix file(see attachment), please check if that is what you want.

    1. Create a measure as below to judge if the resource is available

    Flag = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _selresource =
    SELECTEDVALUE ( 'Resource'[Resource No.] )
VAR _wresources =
    CALCULATETABLE (
        VALUES ( 'Forecast Entry'[Resource No.] ),
        FILTER ( 'Forecast Entry', 'Forecast Entry'[Date] = _seldate )
    )
RETURN
    IF ( NOT ( _selresource IN _wresources ), 1, 0 )

    2. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

    3. Hide the field Flag of the visual by toggle off the option "Text wrap" and adjust the size of the field

    Best Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Dacja ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

    Available resources = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _wresources =
    CALCULATETABLE (
        VALUES ( 'Forecast Entry'[Resource No.] ),
        FILTER ( 'Forecast Entry', 'Forecast Entry'[Date] = _seldate )
    )
RETURN
    CONCATENATEX (
        FILTER ( 'Resource', NOT ( 'Resource'[Resource No.] IN _wresources ) ),
        'Resource'[Resource Name],
        ","
    )

    If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Dacja's avatar
      Dacja
      Frequent Visitor
      4 years ago

      Hey Anonymous ,

       

      thank you very much for your solution, it works and is basically what i was looking for, except that i need a new table with a new row for every entry found:

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Dacja ,

        I updated my sample pbix file(see attachment), please check if that is what you want.

        1. Create a measure as below to judge if the resource is available

        Flag = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _selresource =
    SELECTEDVALUE ( 'Resource'[Resource No.] )
VAR _wresources =
    CALCULATETABLE (
        VALUES ( 'Forecast Entry'[Resource No.] ),
        FILTER ( 'Forecast Entry', 'Forecast Entry'[Date] = _seldate )
    )
RETURN
    IF ( NOT ( _selresource IN _wresources ), 1, 0 )

        2. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

        3. Hide the field Flag of the visual by toggle off the option "Text wrap" and adjust the size of the field

        Best Regards