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