Forum Discussion

lcd's avatar
lcd
Frequent Visitor
9 years ago
Solved

New table with measures values

Hi everyone,  my case is that: - I have a main table with measures based on other fact tables and a calendar table - now I want to build a table with DAX code (with SELECTCOLUMNS) to hold the meas...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi lcd,

     

    It is the expected behavior.

     

    Not like measures, calculate columns/tables(like your myMeasureTable) are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report(like a Slicer).

     

    So it is not possible to create a calculate table which can change dynamically with user selections on the report. :smileyhappy:

     

    Regards

Reid_Havens's avatar
Reid_Havens
Most Valuable Professional
9 years ago

Hi there,

 

So if the DAX measure is written correctly, it should work the same way regardless of what table it is held in. You just need to make sure that any column reference in the DAX measure includes both the table and column name. Unless I'm misunderstanding the question. I just posted an article about measures tables today actually, not sure if that's coincidence or that you read that and ended up here with your question.

 

Reid Havens - Principal Consultant

PowerPivotPro

  • lcd's avatar
    lcd
    Frequent Visitor
    9 years ago

    Thanks Reid, but I need more help and so I give more information:

     

    My 3 tables:

    - EXPENSES: it's a fact table with expenses transactions.

    - CALENDAR: It has all the dates I need and it's linked with EXPENSES by a date column.

    - COMPANY: it's a table with companies info and it's linked with EXPENSES by companyID. In this table I have the measure:

     

    myMeasure = TOTALYTD(sum(EXPENSES[AMOUNT]);CALENDAR[DATE]) 

     

    Now, I need to have a new table with myMeasure:

     

    myMeasureTable = SELECTCOLUMNS(COMPANY;"companyId";COMPANY[companyId];"myMeasureField";COMPANY[myMeasure])

     

    When I use a visual with COMPANY[myMeasure] and a slicer with CALENDAR[DATE], I can control the visual with the slicer.

    But when I use a visual with myMeasureTable, the slicer with CALENDAR[DATE] doesn't work.

     

    Thanks

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi lcd,

       

      It is the expected behavior.

       

      Not like measures, calculate columns/tables(like your myMeasureTable) are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report(like a Slicer).

       

      So it is not possible to create a calculate table which can change dynamically with user selections on the report. :smileyhappy:

       

      Regards

      • lcd's avatar
        lcd
        Frequent Visitor
        9 years ago

        Thanks v-ljerr-msft, I understand. I thought this kind of tables were like measures. I will look for other solution.

        Regards