Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Matrix with Weekly Data Columns and String Values

I'm new to PowerBI and I'm trying to create the visualization below and am having trouble because of a few areas:

 

 

  

1. I am getting an error with dates because there are gaps in the dates I'm using due to them being weekly data.

2. I am having trouble re-formatting dates because PowerBI is putting in a long-winded full date ("Monday, January 1, 2018" instead of "01/01/2018")

3. I have not figured out how to do a countif using strings.

 

Any help or advice would be greatly appreciated.

 

Thanks!

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi  Anonymous,

     

    I'm assuming that you want to return Yes when measure is greater than target.

     

    Use the following measure to add to your matrix:

     

    YES / NO = 
IF (
    HASONEFILTER ( Data[Week Starting] );
    IF ( SUM ( Data[Measure] ) < SUM ( Data[Target] ); "No"; "Yes" );
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Data;
                Data[Week Starting];
                "YES/NO"; SUM ( Data[Measure] ) - SUM ( Data[Target] )
            );
            [YES/NO] > 0
        )
    )
)

    Regards,

    MFelix

  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous,

     

    Select the date column then select the modeling on the bar and format the date as you need.

     

     

     

    Regards,

    MFelix

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi  Anonymous,

     

    I'm assuming that you want to return Yes when measure is greater than target.

     

    Use the following measure to add to your matrix:

     

    YES / NO = 
IF (
    HASONEFILTER ( Data[Week Starting] );
    IF ( SUM ( Data[Measure] ) < SUM ( Data[Target] ); "No"; "Yes" );
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Data;
                Data[Week Starting];
                "YES/NO"; SUM ( Data[Measure] ) - SUM ( Data[Target] )
            );
            [YES/NO] > 0
        )
    )
)

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Felix!! This is great! Any advice on how to adjust the format of the dates as columns?

       

      Cheers,

       

      Julian

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous,

         

        Select the date column then select the modeling on the bar and format the date as you need.

         

         

         

        Regards,

        MFelix