Slicer selection not working in virtual table creation

[GalaBI79]

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

[v-xiaocliu-msft]

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

[v-xiaocliu-msft · Accepted solution]

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]

Hi @v-xiaocliu-msft ,

"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? 🤔

[v-xiaocliu-msft]

Hi @GalaBI79  ,

Please share the sample data and the expected outputs.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,

Wearsky

[GalaBI79]

Hi @v-xiaocliu-msft 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.

InvoiceNumber	InvoiceDate	Type	Brand
108230	29/07/2024	WL	T
405405	29/07/2024	WL	T
457698	29/07/2024	WL	T
331583	29/07/2024	WL	T
497201	29/07/2024	WL	T
469280	29/07/2024	WL	T
13797	29/07/2024	WL	T
282243	29/07/2024	WL	T
268638	29/07/2024	WL	T

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

DATE	DAY_LABEL_ITA	DAY_LABEL_ENG	DAY	MONTH_LABEL_ITA	MONTH_LABEL	MONTH	YEAR	WORK_DAYS	WEEKEND
01/01/2023	Domenica	Sunday	01	Gennaio	January	01	2023	0	1
02/01/2023	Lunedi	Monday	02	Gennaio	January	01	2023	1	0
03/01/2023	Martedì	Tuesday	03	Gennaio	January	01	2023	1	0
04/01/2023	Mercoledì	Wednesday	04	Gennaio	January	01	2023	1	0
05/01/2023	Giovedi	Thursday	05	Gennaio	January	01	2023	1	0
06/01/2023	Venerdì	Friday	06	Gennaio	January	01	2023	0	0
07/01/2023	Sabato	Saturday	07	Gennaio	January	01	2023	0	1
08/01/2023	Domenica	Sunday	08	Gennaio	January	01	2023	0	1
09/01/2023	Lunedi	Monday	09	Gennaio	January	01	2023	1	0

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

[GalaBI79]

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?

[HotChilli]

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.