Forum Discussion

fnyameino's avatar
fnyameino
Frequent Visitor
3 years ago
Solved

Create a custom static table using DAX

I am trying to design this chart in powerbi. The x axis contains different time limits.  Currently this is design in excel and the table below is the source of data   Average Tests   ...
DATATABLE
PowerBI DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi fnyameino

    If your categorization is static, the easiest way would be to add a category column in your record table. Then you could  pull the category column to your plotting x-axis. 

     

    the code to add the column looks like this:

     

    Category =

    SWICH(

        TRUE(),

        "March", TableName[Date]<=DATE(2020,3,31),

        "April 1- 28", TableName[Date]<=DATE(2020,4,28),

        "April 29- July 17", TableName[Date]<=DATE(2020,7,17),

        "July 20- Oct 9 2020", TableName[Date]>=DATE(2020,7,20)&&TableName[Date]<=DATE(2020,10,9),

        "Oct 12 2020- Nov 11, 2022", TableName[Date]>=DATE(2020,10,12)&&TableName[Date]<=DATE(2022,11,11)

    )

     

    if you have a dynamic grouping, say the way of categorization changes periodically and you find the change in DAX code is an issue, you may import the category table into Power BI and related with the record table. 

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi fnyameino

If your categorization is static, the easiest way would be to add a category column in your record table. Then you could  pull the category column to your plotting x-axis. 

 

the code to add the column looks like this:

 

Category =

SWICH(

    TRUE(),

    "March", TableName[Date]<=DATE(2020,3,31),

    "April 1- 28", TableName[Date]<=DATE(2020,4,28),

    "April 29- July 17", TableName[Date]<=DATE(2020,7,17),

    "July 20- Oct 9 2020", TableName[Date]>=DATE(2020,7,20)&&TableName[Date]<=DATE(2020,10,9),

    "Oct 12 2020- Nov 11, 2022", TableName[Date]>=DATE(2020,10,12)&&TableName[Date]<=DATE(2022,11,11)

)

 

if you have a dynamic grouping, say the way of categorization changes periodically and you find the change in DAX code is an issue, you may import the category table into Power BI and related with the record table. 

 

  • fnyameino's avatar
    fnyameino
    Frequent Visitor
    3 years ago

    This worked the magic

    Category =
    VAR DateNow = TODAY()
    RETURN
    SWITCH(
        TRUE(),
        CovidTesting[Date]<=DATE(2020,3,31),"1. March" ,
        CovidTesting[Date]<=DATE(2020,4,28), "2. April 1-28",
        CovidTesting[Date]<=DATE(2020,7,17), "3. April 29-July 17",
        CovidTesting[Date]>=DATE(2020,7,20)&&CovidTesting[Date]<=DATE(2020,10,9), "4. July 20-Oct 9 2020",
        CovidTesting[Date]>=DATE(2020,10,12)&&CovidTesting[Date]<= TODAY(),"5. Oct 12 2020-Dec 13 2022"