Forum Discussion

GalaBI79's avatar
GalaBI79
Regular Visitor
1 year ago
Solved

Slicer selection not working in virtual table creation

Hi all,

 

First of all, apologies for my english, but it's not my first language 😁

 

I'm new to Power BI so sorry for any mistakes.

 

I have a table with invoices, organized by invoice date. I created a "Calendar" table with the mapping of "day", "month", "year" (and other columns not important in this context) up to 2032, just to be safe.

I imported the two tables and linked them via a Many to 1 Single relationship between the Calendario[DATE] field and the Fatture[InvoiceDate] field.

I created three slicers, for YEAR, MONTH and DATE from the Calendar table

I created the following measures:


1) If I have not selected any specific date returns the date selected by the slicer or the max available based on year and month
Max_Date = CALCULATE(

MAXX(

VALUES(Fatture[InvoiceDate]),
FORMAT(Fatture[InvoiceDate], "dd/MM/yyyy")
)
)

2) The Formula count discounted invoices

Count_Fatt_Scont =
CALCULATE(

DISTINCTCOUNT(Fatture[InvoiceNumber]),

Fatture[Type]="SCONTI"
)

 

3)The Formula count the invoices sold
Count_Fatt_Sale =

CALCULATE(

DISTINCTCOUNT(Fatture[InvoiceNumber]),

Fatture[Type]="SALE"

)

 

the 3 measures were created and added to the table "Fattura".

 

Display requirement: a matrix that has the values ​​TODAY, MONTH and MONTH TO DATE in ROWS, and the invoice count in VALUE. The purpose is to show the invoices for the selected day or the last available day for the selected month, the count of all invoices for the selected month and the count of invoices from the beginning of the year to the selected date (or the maximum date for the selected month.

 

 

I therefore decided to create a virtual table in UNION composed as follows:

 

TB_Fatture_All =

VAR DATA_MAX = Max_Date

RETURN

UNION(

SELECTCOLUMNS(

Fatture,

"ORDER_DATE", 1,

"REF_DATE_LABEL", "TODAY",

"ACTUAL", CALCULATE(

[Count_Fatt_Sale] - [Count_Fatt_Scont],

FORMAT(Fatture[InvoiceDate], "dd/MM/yyyy") = DATA_MAX

),

"BRAND", Fatture[Brand]

),

SELECTCOLUMNS(

Fatture,

"ORDER_DATE", 3,

"REF_DATE_LABEL", "MONTH",

"ACTUAL", CALCULATE(

[Count_Fatt_Sale] - [Count_Fatt_Scont],

MONTH(Fatture[InvoiceDate]) = MONTH(DATA_MAX)

),

"BRAND", Fatture[Brand]

),

SELECTCOLUMNS(

Fatture,

"ORDER_DATE", 2,

"REF_DATE_LABEL", "MONTH TO DATE",

"ACTUAL", CALCULATE(

[Count_Fatt_Sale] - [Count_Fatt_Scont]

,MONTH(Fatture[InvoiceDate]) <= MONTH(DATA_MAX)

),

"BRAND", Fatture[Brand]

)

)

 

The virtual table, written like this, does not read the value of DATA_MAX, I also tried to directly put the value of CALENDAR (SELECTEDVALUE(Calendar[DATE]) and SELECTEDVALUE(Calendar[MONTH])) but nothing. If I insert the Max_Date measure into a CARD it behaves exactly as I need it, it displays the date I selected in the slicer and if I do not select anything it shows me the max date with respect to the selected month.

 

I tried everything but I do not understand why it is not seen in the virtual table and how to solve it. Could you help me?

 

Thanks
Nicola

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi GalaBI79 ,

     

    I made a sample for you.

    Measure = 
var _column = MAX('Table3'[Column1])
var _value= SWITCH(TRUE(),
_column="Today"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=SELECTEDVALUE('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(SELECTEDVALUE('Calendar'[Date]))),
_column="Today"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=MAX('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(MIN('Calendar'[Date])))
)
RETURN _value

    Note: This measure only considers the month and does not consider the year as a filter condition.

     

    Best Regards,

    Wearsky

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi GalaBI79 ,

     

    I made a sample for you.

    Measure = 
var _column = MAX('Table3'[Column1])
var _value= SWITCH(TRUE(),
_column="Today"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=SELECTEDVALUE('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(SELECTEDVALUE('Calendar'[Date]))),
_column="Today"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=MAX('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(MIN('Calendar'[Date])))
)
RETURN _value

    Note: This measure only considers the month and does not consider the year as a filter condition.

     

    Best Regards,

    Wearsky

    • GalaBI79's avatar
      GalaBI79
      Regular Visitor
      1 year ago

      Hi Anonymous ,

       

      "habemus papam" 😁

       

      Your solution is the right one and it works great, tried it with my tables and the filters finally work correctly.

       

      I just have one question, due to my ignorance, I saw that you didn't join the tables with each other. I noticed that in my report, if the invoice table and Calendar are in relation, your filter does not work, but if I remove the relation it works. Why? 🤔

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    1 year ago

    TB_Fatture_All is not a virtual table, it is a table. Tables and calculated columns are not affected by slicers. They are created at 'refresh' time. Slicers affect visuals.

    It is not a good idea to use measures to create calculated columns or tables.

  • GalaBI79's avatar
    GalaBI79
    Regular Visitor
    1 year ago

    Hello and thanks for the reply,

     

    I used measures because I couldn't find a better way to make the three values ​​(TODAY, MONTH and MONTH TO DATE) appear all one under the other, as a single column.

     

     

    Do you think there is another way to represent the data without using measures?

    • GalaBI79's avatar
      GalaBI79
      Regular Visitor
      1 year ago

      Hi Anonymous and thanks for the reply,

       

      here is an example of the main table. For privacy reasons I had to remove some columns, the ones in the example are the ones I use in the formulas.

       

      InvoiceNumberInvoiceDateTypeBrand
      10823029/07/2024WLT
      40540529/07/2024WLT
      45769829/07/2024WLT
      33158329/07/2024WLT
      49720129/07/2024WLT
      46928029/07/2024WLT
      1379729/07/2024WLT
      28224329/07/2024WLT
      26863829/07/2024WLT

       

      Below is an example of the "Calendar" table, which I use to manage filters in slicers:

       

      DATEDAY_LABEL_ITADAY_LABEL_ENGDAYMONTH_LABEL_ITAMONTH_LABELMONTHYEARWORK_DAYSWEEKEND
      01/01/2023DomenicaSunday01GennaioJanuary01202301
      02/01/2023LunediMonday02GennaioJanuary01202310
      03/01/2023MartedìTuesday03GennaioJanuary01202310
      04/01/2023MercoledìWednesday04GennaioJanuary01202310
      05/01/2023GiovediThursday05GennaioJanuary01202310
      06/01/2023VenerdìFriday06GennaioJanuary01202300
      07/01/2023SabatoSaturday07GennaioJanuary01202301
      08/01/2023DomenicaSunday08GennaioJanuary01202301
      09/01/2023LunediMonday09GennaioJanuary01202310

       

      The main table and the Calendar table are connected by "Many to one" relationship and "Single" direction. I also tried with "Both" but nothing changed.

       

       

      These are the expected results. All three rows should vary depending on the filters YEAR, MONTH and DAY.

       

      Ex. if I select 31/07/2024, I should see:

      • Today: data from 31/07/2024;
      • Month: data from the month of July;
      • Month to date: data from January to July.

      If I select only the month and not the single day, I should see the maximum date for the selected month.

       

      If I create 3 separate custom tables and put the data in one table, everything works. If I create a custom table in union from these tables, I lose all the filters.

       

      Best regards,

      Nicola

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi GalaBI79 ,

     

    Because your requirements conflict with the logic of the table relationship. Creating table relationships can result in incorrect filter context.

    Hope it makes sense.

     

    Best Regards,

    Wearsky