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...
  • 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

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